package courseScheduler;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 *
 * @author lyn5023
 */
public class Course {
    int id;
    int courseNumber;
    String coursePrefix;

    Connection conn;

    PreparedStatement courseGetStmnt;
    PreparedStatement sectionSetStmnt;
    PreparedStatement sectionInsertStmnt;


    /**
     * Constructor - Section
     * @param newConn
     * @param ID
     * @throws SQLException
     */
    Course (Connection newConn, int ID) throws SQLException
    {
        conn = newConn;
        conn.setAutoCommit(false);
        prepareGetStatement();
        courseGetStmnt.setInt(1, ID);
        java.sql.ResultSet rs = courseGetStmnt.executeQuery();
        if(rs.first()){
            int count = 0;
            do{
                count ++;
            } while (rs.next());
            rs.first();
            if (count == 1){
                setCoursePrefix(rs.getString(1));
                setCourseNumber(rs.getInt(2));
                id = ID;
            }
            else {
                javax.swing.JOptionPane.showMessageDialog(null, "Can not process; two entries found");
            }
        }
    }



    public final void setCourseNumber(int courseNum){
        courseNumber = courseNum;
    }

    public final void setCoursePrefix(String coursePref){
        coursePrefix = coursePref;
    }

    public final void setID(int newID){
        id = newID;
    }

    public int getCourseNumber(){
        return courseNumber;
    }

    public String getCoursePrefix(){
        return coursePrefix;
    }

    public int getID(){
        return id;
    }

    public void setConnection(java.sql.Connection newConn){
        conn = newConn;
    }
    
    /**
     * Method to prepare update statement
     * @throws SQLException
     */
    private void prepareGetStatement() throws SQLException{
        courseGetStmnt = conn.prepareStatement
        (
            "SELECT course.type_, course.number "
                + "FROM course "
                + "WHERE id = ?;"
        );
    }


    /**
     * Method to prepare update statement
     * @throws SQLException
     */
    private void prepareSetStatement() throws SQLException{
        sectionSetStmnt = conn.prepareStatement
        (
            "UPDATE course "
            + "(course.type_, course.number) "
            + "VALUES (\"" + getCoursePrefix() + "\", " + getCourseNumber() + ") "
            + "WHERE id = ?;"
        );
    }


    /**
     * Method to prepare update statement
     * @throws SQLException
     */
    private void prepareInsertStatement() throws SQLException{
        sectionInsertStmnt = conn.prepareStatement
        (
            "INSERT INTO course "
            + "(course.type_, course.number) "
            + "VALUES (\"" + getCoursePrefix() + "\", " + getCourseNumber() + "); "
        );
    }

    public static int getID(java.sql.Connection newConn, String sPrefix, int sNumber) throws java.sql.SQLException{
        java.sql.Statement stmnt;
        java.sql.ResultSet rs;
        stmnt = newConn.createStatement();
        rs = stmnt.executeQuery("SELECT id FROM course WHERE "
                + "type_ LIKE \"" + sPrefix + "\" AND sNumber = " + sNumber + ";");
        rs.first();
        return rs.getInt(1);
    }

    public static String getType(java.sql.Connection newConn, int id) throws java.sql.SQLException{
        java.sql.Statement stmnt;
        java.sql.ResultSet rs;
        stmnt = newConn.createStatement();
        int count = 0;
        rs = stmnt.executeQuery("SELECT type_ FROM course WHERE id = " + id + ";");
        rs.last();
        String returnString;
        rs.first();
        returnString = rs.getString(1);
        return returnString;
    }

    public static String[] getNum(java.sql.Connection newConn, int id) throws java.sql.SQLException{
        java.sql.Statement stmnt;
        java.sql.ResultSet rs;
        stmnt = newConn.createStatement();
        int count = 0;
        rs = stmnt.executeQuery("SELECT number FROM course WHERE type_ = " + id + ";");
        rs.last();
        String [] returnArray = new String [rs.getRow()];
        rs.beforeFirst();
        while (rs.next()){
            returnArray [count] = rs.getString("number");
            count ++;
        }
        return returnArray;
    }

}
